with tms as (
    select
         waybill_no
          ,network_code
          ,network_type
          ,previous_network_code
          ,previous_network_later_time
          ,next_network_code
          ,next_network_early_time
          ,all_rank_asc
          ,all_rank_desc
          ,center_rank_asc
          ,center_rank_desc
          ,entreport_rank_asc
          ,entreport_rank_desc
          ,all_cnt
          ,network_later_scantime
          ,network_early_scantime
          ,network_later_scantype
          ,network_early_scantype
          ,actual_taking_scantime
          ,actual_taking_weight
          ,actual_taking_scantype
          ,taking_later_scantime
          ,taking_early_scantime
          ,taking_early_weight
          ,warehousing_later_scantime
          ,warehousing_early_scantime
          ,warehousing_early_weight
          ,bagging_early_scantime
          ,bagging_later_scantime
          ,bagging_later_package_code
          ,unloading_later_scantime
          ,unloading_early_scantime
          ,unloading_early_weight
          ,arrival_later_scantime
          ,arrival_early_scantime
          ,arrival_early_weight
          ,unloading_arrival_shipment_no
          ,unloading_arrival_planned_departure_time
          ,unloading_arrival_planned_arrival_time
          ,unloading_arrival_actual_departure_time
          ,unloading_arrival_actual_arrival_time
          ,loading_early_scantime
          ,loading_later_scantime
          ,loading_later_weight
          ,send_early_scantime
          ,send_later_scantime
          ,send_later_weight
          ,loading_send_shipment_no
          ,loading_send_planned_departure_time
          ,loading_send_planned_arrival_time
          ,loading_send_actual_departure_time
          ,loading_send_actual_arrival_time
          ,loading_send_next_network_code
          ,loading_send_next_network_type
          ,deliver_early_scantime
          ,deliver_later_scantime
          ,deliver_later_weight
          ,sign_later_scantime
          ,sign_early_scantime
          ,sign_early_weight
          ,actual_sign_scantime
          ,actual_sign_weight
          ,actual_sign_scantype
          ,aging_sign_scantime
          ,aging_sign_weight
          ,aging_sign_scantype
          ,null as first_code
          ,null as second_code
          ,null as third_code
          ,null as is_sign
          ,null as waybill_source_code
          ,null as waybill_source_name
          ,null as is_void
          ,null as is_refund
          ,null as pick_network_code
          ,null as dispatch_network_code
          ,null as input_network_code
          ,null as order_source_code
          ,null as is_delete
          ,null as goods_name
          ,null as is_abnormal
          ,null as waybill_weight
          ,null as settlement_weight
          ,null as statistics_order_source
          ,null as goods_type_code
          ,null as goods_type_name
          ,dt
   from jms_dwd.dwd_s01_whole_operations_basic_agg_dt
      where dt between date_add('{{ execution_date | cst_ds }}', -16) and '{{ execution_date | cst_ds }}'

  union all

    select
           waybill_no
          ,null as network_code
          ,null as network_type
          ,null as previous_network_code
          ,null as previous_network_later_time
          ,null as next_network_code
          ,null as next_network_early_time
          ,null as all_rank_asc
          ,null as all_rank_desc
          ,null as center_rank_asc
          ,null as center_rank_desc
          ,null as entreport_rank_asc
          ,null as entreport_rank_desc
          ,null as all_cnt
          ,null as network_later_scantime
          ,null as network_early_scantime
          ,null as network_later_scantype
          ,null as network_early_scantype
          ,null as actual_taking_scantime
          ,null as actual_taking_weight
          ,null as actual_taking_scantype
          ,null as taking_later_scantime
          ,null as taking_early_scantime
          ,null as taking_early_weight
          ,null as warehousing_later_scantime
          ,null as warehousing_early_scantime
          ,null as warehousing_early_weight
          ,null as bagging_early_scantime
          ,null as bagging_later_scantime
          ,null as bagging_later_package_code
          ,null as unloading_later_scantime
          ,null as unloading_early_scantime
          ,null as unloading_early_weight
          ,null as arrival_later_scantime
          ,null as arrival_early_scantime
          ,null as arrival_early_weight
          ,null as unloading_arrival_shipment_no
          ,null as unloading_arrival_planned_departure_time
          ,null as unloading_arrival_planned_arrival_time
          ,null as unloading_arrival_actual_departure_time
          ,null as unloading_arrival_actual_arrival_time
          ,null as loading_early_scantime
          ,null as loading_later_scantime
          ,null as loading_later_weight
          ,null as send_early_scantime
          ,null as send_later_scantime
          ,null as send_later_weight
          ,null as loading_send_shipment_no
          ,null as loading_send_planned_departure_time
          ,null as loading_send_planned_arrival_time
          ,null as loading_send_actual_departure_time
          ,null as loading_send_actual_arrival_time
          ,null as loading_send_next_network_code
          ,null as loading_send_next_network_type
          ,null as deliver_early_scantime
          ,null as deliver_later_scantime
          ,null as deliver_later_weight
          ,null as sign_later_scantime
          ,null as sign_early_scantime
          ,null as sign_early_weight
          ,null as actual_sign_scantime
          ,null as actual_sign_weight
          ,null as actual_sign_scantype
          ,null as aging_sign_scantime
          ,null as aging_sign_weight
          ,null as aging_sign_scantype
          ,first_code
          ,second_code
          ,third_code
          ,is_sign
          ,waybill_source_code
          ,waybill_source_name
          ,is_void
          ,is_refund
          ,pick_network_code
          ,dispatch_network_code
          ,input_network_code
          ,order_source_code
          ,is_delete
          ,goods_name
          ,is_abnormal
          ,waybill_weight
          ,settlement_weight
          ,null as statistics_order_source
          ,goods_type_code
          ,goods_type_name
          ,'1900-01-01' as dt
        from (
          select s.*,row_number() over(partition by waybill_no order by dt desc) as rank
                 from jms_dwd.dwd_yl_oms_oms_waybill_dt s
               where dt between date_add('{{ execution_date | cst_ds }}', -18) and '{{ execution_date | cst_ds }}'
     ) tmp where rank = 1
union all
       select
           waybill_no
          ,null as network_code
          ,null as network_type
          ,null as previous_network_code
          ,null as previous_network_later_time
          ,null as next_network_code
          ,null as next_network_early_time
          ,null as all_rank_asc
          ,null as all_rank_desc
          ,null as center_rank_asc
          ,null as center_rank_desc
          ,null as entreport_rank_asc
          ,null as entreport_rank_desc
          ,null as all_cnt
          ,null as network_later_scantime
          ,null as network_early_scantime
          ,null as network_later_scantype
          ,null as network_early_scantype
          ,null as actual_taking_scantime
          ,null as actual_taking_weight
          ,null as actual_taking_scantype
          ,null as taking_later_scantime
          ,null as taking_early_scantime
          ,null as taking_early_weight
          ,null as warehousing_later_scantime
          ,null as warehousing_early_scantime
          ,null as warehousing_early_weight
          ,null as bagging_early_scantime
          ,null as bagging_later_scantime
          ,null as bagging_later_package_code
          ,null as unloading_later_scantime
          ,null as unloading_early_scantime
          ,null as unloading_early_weight
          ,null as arrival_later_scantime
          ,null as arrival_early_scantime
          ,null as arrival_early_weight
          ,null as unloading_arrival_shipment_no
          ,null as unloading_arrival_planned_departure_time
          ,null as unloading_arrival_planned_arrival_time
          ,null as unloading_arrival_actual_departure_time
          ,null as unloading_arrival_actual_arrival_time
          ,null as loading_early_scantime
          ,null as loading_later_scantime
          ,null as loading_later_weight
          ,null as send_early_scantime
          ,null as send_later_scantime
          ,null as send_later_weight
          ,null as loading_send_shipment_no
          ,null as loading_send_planned_departure_time
          ,null as loading_send_planned_arrival_time
          ,null as loading_send_actual_departure_time
          ,null as loading_send_actual_arrival_time
          ,null as loading_send_next_network_code
          ,null as loading_send_next_network_type
          ,null as deliver_early_scantime
          ,null as deliver_later_scantime
          ,null as deliver_later_weight
          ,null as sign_later_scantime
          ,null as sign_early_scantime
          ,null as sign_early_weight
          ,null as actual_sign_scantime
          ,null as actual_sign_weight
          ,null as actual_sign_scantype
          ,null as aging_sign_scantime
          ,null as aging_sign_weight
          ,null as aging_sign_scantype
          ,null as first_code
          ,null as second_code
          ,null as third_code
          ,null as is_sign
          ,null as waybill_source_code
          ,null as waybill_source_name
          ,null as is_void
          ,null as is_refund
          ,null as pick_network_code
          ,null as dispatch_network_code
          ,null as input_network_code
          ,null as order_source_code
          ,null as is_delete
          ,null as goods_name
          ,null as is_abnormal
          ,null as waybill_weight
          ,null as settlement_weight
          ,statistics_order_source
          ,null as goods_type_code
          ,null as goods_type_name
          ,'1900-01-01' as dt
              from
         ( select way_bill_no as waybill_no,order_source as statistics_order_source,row_number() over(partition by way_bill_no order by id desc)  as rank
                from jms_ods.tab_statistics s
                where dt BETWEEN date_add('{{ execution_date | cst_ds }}', -18) AND '{{ execution_date | cst_ds }}'
          ) tmp  where rank = 1
),

tab as (
   select           waybill_no,                --  运单号
                    network_code,              --  网点编码
                    network_type,              --  网点类型
                    all_rank_asc ,             --  所有网点排序(正序)
                    all_rank_desc ,            --  所有网点排序(倒序)
                    center_rank_asc ,          --  转运中心排序(正序)
                    center_rank_desc,          --  转运中心排序(倒序)
                    entreport_rank_asc,        --  集散网点排序(正序)
                    entreport_rank_desc,       --  集散网点排序(倒序)
                    entreport_rank_asc=1  as is_first_entreport,        --  是否始发集散点
                    entreport_rank_desc=1 as is_end_entreport,          --  始发末端集散点
                    all_cnt ,                  --  扫描次数
                    network_later_scantime,    --  网点最后扫描时间
                    network_early_scantime,    --  网点最早扫描时间
                    network_later_scantype,    --  网点最后扫描类型
                    network_early_scantype,    --  网点最早扫描类型
                    taking_later_scantime,     --  网点最晚揽收时间
                    if(taking_early_scantime is null ,null,
                         named_struct('scantime',taking_early_scantime,
                                 'weight',taking_early_weight,
                                 'network_code',network_code))
                                   as taking_early_cols  ,                    -- 网点最早揽收
                    warehousing_later_scantime,
                    if(warehousing_early_scantime is null ,null,
                       named_struct('scantime',warehousing_early_scantime,
                                 'weight',warehousing_early_weight,
                                 'network_code',network_code))
                                   as warehousing_early_cols  ,               -- 网点最早入仓
                    bagging_early_scantime,
                    if(bagging_later_scantime is null ,null,
                       named_struct('scantime',bagging_later_scantime,
                                 'package_code',bagging_later_package_code,
                                 'network_code',network_code))
                                   as bagging_later_cols  ,                   -- 网点最早装袋

                    arrival_later_scantime,                                   -- 最晚到件时间
                    arrival_early_scantime,                                   -- 最早到件时间
                    arrival_early_weight,                                     -- 到件重量

                    unloading_later_scantime,                                 --  最晚卸车时间
                    unloading_early_scantime,                                 --  最早卸车时间
                    unloading_early_weight,                                   --  卸车重量

                    unloading_arrival_shipment_no  ,                           --  卸车到件任务号
                    unloading_arrival_planned_departure_time,                                   --  卸车到件规划发车时间
                    unloading_arrival_planned_arrival_time,                                   --  卸车到件规划到车时间
                    unloading_arrival_actual_departure_time,                                   --  卸车到件实际发车时间
                    unloading_arrival_actual_arrival_time,                                   --  卸车到件实际到车时间

                    loading_early_scantime,                                           --  最晚装车时间
                    loading_later_scantime,                                           --  最早装车时间
                    loading_later_weight,                                             --  装车重量

                    send_early_scantime,                                              --  最晚发件时间
                    send_later_scantime,                                              --  最早发件时间
                    send_later_weight,                                                --  发件重量

                    loading_send_shipment_no  ,                                   --  装车发件任务号
                    loading_send_planned_departure_time    ,                        --  装车发件规划发车时间
                    loading_send_planned_arrival_time,                             --  装车发件规划到车时间
                    loading_send_actual_departure_time,                            --  装车发件实际发车时间
                    loading_send_actual_arrival_time,                            --  装车发件实际到车时间
                    loading_send_next_network_code,                            --  装车发件下一站
                    loading_send_next_network_type,                            --  装车发件下一站网点类型

                    deliver_early_scantime,                                           --  网点最早派件时间
                    if(deliver_later_scantime is null ,null,
                       named_struct('scantime',deliver_later_scantime,
                                 'weight',deliver_later_weight,
                                 'network_code',network_code))
                                   as deliver_later_cols  ,                           --  最晚派件
                    sign_later_scantime,                                              --  最晚签收时间
                    if(sign_early_scantime is null ,null,
                       named_struct('scantime',sign_early_scantime,
                                 'weight',sign_early_weight,
                                 'network_code',network_code))
                                   as sign_early_cols  ,                              --  最早签收
                    if(actual_sign_scantime is null ,null,
                       named_struct('scantime',actual_sign_scantime,
                                 'weight',actual_sign_weight,
                                 'scantype',actual_sign_scantype,
                                 'network_code',network_code))
                                   as actual_sign_cols  ,                             --  实际签收
                    if(aging_sign_scantime is null ,null,
                       named_struct('scantime',aging_sign_scantime,
                                 'weight',aging_sign_weight,
                                 'scantype',aging_sign_scantype,
                                 'network_code',network_code))
                                   as aging_sign_cols  ,                              --  时效签收
                    if(actual_taking_scantime is null ,null,
                       named_struct('scantime',actual_taking_scantime,
                                 'weight',actual_taking_weight,
                                 'scantype',actual_taking_scantype,
                                 'network_code',network_code))
                                   as actual_taking_cols                             --  实际揽收
                    ,first_code
                    ,second_code
                    ,third_code
                    ,is_sign
                    ,waybill_source_code
                    ,waybill_source_name
                    ,is_void
                    ,is_refund
                    ,pick_network_code
                    ,dispatch_network_code
                    ,input_network_code
                    ,order_source_code
                    ,is_delete
                    ,goods_name
                    ,is_abnormal
                    ,waybill_weight
                    ,settlement_weight
                    ,statistics_order_source
                    ,goods_type_code
                    ,goods_type_name
                    ,dt
    from tms
),
operat as (
select
waybill_no                                                                                                 as waybill_no
,min(network_early_scantime)                                                                               as waybill_first_scantime                               -- 最早操作时间
,max(if(all_rank_asc = 1, network_early_scantype,null))                                                    as waybill_first_scantype                               -- 最早扫描类型
,max(network_later_scantime)                                                                               as waybill_end_scantime                                 -- 最晚操作时间
,max(if(all_rank_desc =1, network_later_scantype,null))                                                    as waybill_end_scantype                                 -- 最晚操作类型
,sum(all_cnt)                                                                                              as all_operation_times                                  -- 运单扫描总次数
,sum(if(network_type=2,all_cnt,0))                                                                         as center_operation_times                               -- 中心扫描总次数
,sum(if(network_type=3,all_cnt,0))                                                                         as entrepot_operation_times                             -- 集散网点扫描次数
,count(network_code)                                                                                       as all_pass_netowrk_times                               -- 运单总经过网点数
,sum(if(network_type=2,1,0))                                                                               as pass_center_times                                    -- 运单总经过中心数
,sum(if(network_type=3,1,0))                                                                               as pass_entrepot_times                                  -- 运单总经过集散网点数
,min(actual_taking_cols).scantime                                                                          as actual_taking_scantime                               -- 实际揽收时间
,min(actual_taking_cols).network_code                                                                      as actual_taking_scansitecode                           -- 实际揽收网点
,min(actual_taking_cols).weight                                                                            as actual_taking_weight                                 -- 实际揽收重量
,min(actual_taking_cols).scantype                                                                          as actual_taking_scantype                               -- 实际揽收扫描类型
,max(taking_later_scantime)                                                                                as taking_later_scantime                                -- 最晚揽收时间
,min(taking_early_cols).scantime                                                                           as taking_early_scantime                                -- 最早揽收时间
,min(taking_early_cols).network_code                                                                       as taking_early_scansitecode                            -- 最早揽收网点
,min(taking_early_cols).weight                                                                             as taking_early_weight
,max(warehousing_later_scantime)                                                                           as warehousing_later_scantime                           -- 最晚入库时间
,min(warehousing_early_cols).scantime                                                                      as warehousing_early_scantime                           -- 最早入库时间
,min(warehousing_early_cols).network_code                                                                  as warehousing_early_scansitecode                       -- 最早入库网点
,min(warehousing_early_cols).weight                                                                        as warehousing_early_weight
,min(bagging_early_scantime)                                                                               as bagging_early_scantime                               -- 最晚装袋时间
,max(bagging_later_cols).scantime                                                                          as bagging_later_scantime                               -- 最早装袋时间
,max(bagging_later_cols).network_code                                                                      as bagging_later_scansitecode                           -- 最早装袋网点
,max(bagging_later_cols).package_code                                                                      as bagging_later_package_code                           -- 最早装袋重量

,max(if(all_rank_asc=1 and network_type in (3,4), network_code, null))                                     as first_network_network_code                           -- 始发网点网点编码
,max(if(all_rank_asc=1 and network_type in (3,4), loading_later_scantime, null))                           as first_network_loading_later_time                     -- 始发网点最晚装车时间
,max(if(all_rank_asc=1 and network_type in (3,4), loading_later_weight, null))                             as first_network_loading_later_weight                   -- 始发网点最晚装车扫描对应重量

,max(if(all_rank_asc=1 and network_type in (3,4), send_later_scantime, null))                              as first_network_send_later_time                        -- 始发网点最晚发车时间
,max(if(all_rank_asc=1 and network_type in (3,4), send_later_weight, null))                                as first_network_send_later_weight                      -- 始发网点最晚发车扫描对应重量

,max(if(all_rank_asc=1 and network_type in (3,4), loading_send_shipment_no, null))                         as first_network_loading_send_shipment_no              -- 始发网点最晚装车发件扫描对应任务号
,max(if(all_rank_asc=1 and network_type in (3,4), loading_send_planned_departure_time, null))              as first_network_loading_send_planned_departure_time   -- 始发网点最晚装车发件规划发车时间
,max(if(all_rank_asc=1 and network_type in (3,4), loading_send_planned_arrival_time, null))                as first_network_loading_send_planned_arrival_time     -- 始发网点最晚装车发件规划到车时间
,max(if(all_rank_asc=1 and network_type in (3,4), loading_send_actual_departure_time, null))               as first_network_loading_send_actual_departure_time    -- 始发网点最晚装车发件实际发车时间
,max(if(all_rank_asc=1 and network_type in (3,4), loading_send_actual_arrival_time, null))                 as first_network_loading_send_actual_arrival_time      -- 始发网点最晚装车发件实际到车时间
,max(if(all_rank_asc=1 and network_type in (3,4), loading_send_next_network_code, null))                   as first_network_loading_send_next_network_code        -- 始发网点最晚装车发件下一站网点编码
,max(if(all_rank_asc=1 and network_type in (3,4), loading_send_next_network_type, null))                   as first_network_loading_send_next_network_type        -- 始发网点最晚装车发件下一站网点类型


,min(if(is_first_entreport, network_code , null ))                                                         as first_nodal_network_code                             -- 始发集散网点
,min(if(is_first_entreport, unloading_early_scantime , null ))                                             as first_nodal_unloading_early_time                     -- 始发集散最早卸车时间
,min(if(is_first_entreport, unloading_early_weight , null ))                                               as first_nodal_unloading_early_weight                   -- 始发集散最早卸车扫描对应重量
,min(if(is_first_entreport, arrival_early_scantime , null ))                                               as first_nodal_arrival_early_time                       -- 始发集散最早到件时间
,min(if(is_first_entreport, arrival_early_weight , null ))                                                 as first_nodal_arrival_early_weight                     -- 始发集散最早到件扫描对应重量

,min(if(is_first_entreport, unloading_arrival_shipment_no             , null ))                            as first_nodal_unloading_arrival_shipment_no            -- 始发集散最早卸车到件扫描对应任务号
,min(if(is_first_entreport, unloading_arrival_planned_departure_time  , null ))                            as first_nodal_unloading_arrival_planned_departure_time -- 始发集散最早卸车到件规划发车时间
,min(if(is_first_entreport, unloading_arrival_planned_arrival_time    , null ))                            as first_nodal_unloading_arrival_planned_arrival_time   -- 始发集散最早卸车到件规划到车时间
,min(if(is_first_entreport, unloading_arrival_actual_departure_time   , null ))                            as first_nodal_unloading_arrival_actual_departure_time  -- 始发集散最早卸车到件实际发车时间
,min(if(is_first_entreport, unloading_arrival_actual_arrival_time     , null ))                            as first_nodal_unloading_arrival_actual_arrival_time    -- 始发集散最早卸车到件实际到车时间

,min(if(is_first_entreport, loading_later_scantime , null ))                                               as first_nodal_loading_later_time                       -- 始发集散最晚装车时间
,min(if(is_first_entreport, loading_later_weight , null ))                                                 as first_nodal_loading_later_weight                     -- 始发集散最晚装车扫描对应重量

,min(if(is_first_entreport, send_later_scantime , null ))                                                  as first_nodal_send_later_time                          -- 始发集散最晚发车时间
,min(if(is_first_entreport, send_later_weight , null ))                                                    as first_nodal_send_later_weight                        -- 始发集散最晚发车扫描对应重量

,min(if(is_first_entreport, loading_send_shipment_no            , null ))                                  as first_nodal_loading_send_shipment_no                        -- 始发集散最晚装车发件扫描对应任务号
,min(if(is_first_entreport, loading_send_planned_departure_time , null ))                                  as first_nodal_loading_send_planned_departure_time             -- 始发集散最晚装车发件规划发车时间
,min(if(is_first_entreport, loading_send_planned_arrival_time   , null ))                                  as first_nodal_loading_send_planned_arrival_time               -- 始发集散最晚装车发件规划到车时间
,min(if(is_first_entreport, loading_send_actual_departure_time  , null ))                                  as first_nodal_loading_send_actual_departure_time              -- 始发集散最晚装车发件实际发车时间
,min(if(is_first_entreport, loading_send_actual_arrival_time    , null ))                                  as first_nodal_loading_send_actual_arrival_time                -- 始发集散最晚装车发件实际到车时间
,min(if(is_first_entreport, loading_send_next_network_code      , null ))                                  as first_nodal_loading_send_next_network_code                  -- 始发集散最晚装车发件下一站网点编码
,min(if(is_first_entreport, loading_send_next_network_type      , null ))                                  as first_nodal_loading_send_next_network_type                  -- 始发集散最晚装车发件下一站网点类型

,max(if(center_rank_asc=1, network_code, null))                                                            as first_center_network_code                    -- 始发转运中心
,max(if(center_rank_asc=1, unloading_early_scantime, null))                                                as first_center_unloading_early_time            -- 始发中心最早卸车时间
,max(if(center_rank_asc=1, unloading_early_weight, null))                                                  as first_center_unloading_early_weight          -- 始发中心最早卸车扫描对应重量
,max(if(center_rank_asc=1, arrival_early_scantime, null))                                                  as first_center_arrival_early_time              -- 始发中心最早到件时间
,max(if(center_rank_asc=1, arrival_early_weight, null))                                                    as first_center_arrival_early_weight            -- 始发中心最早到件扫描对应重量

,max(if(center_rank_asc=1, unloading_arrival_shipment_no            , null))                               as first_center_unloading_arrival_shipment_no                   -- 始发中心最早卸车到件扫描对应任务号
,max(if(center_rank_asc=1, unloading_arrival_planned_departure_time , null))                               as first_center_unloading_arrival_planned_departure_time        -- 始发中心最早卸车到件规划发车时间
,max(if(center_rank_asc=1, unloading_arrival_planned_arrival_time   , null))                               as first_center_unloading_arrival_planned_arrival_time          -- 始发中心最早卸车到件规划到车时间
,max(if(center_rank_asc=1, unloading_arrival_actual_departure_time  , null))                               as first_center_unloading_arrival_actual_departure_time         -- 始发中心最早卸车到件实际发车时间
,max(if(center_rank_asc=1, unloading_arrival_actual_arrival_time    , null))                               as first_center_unloading_arrival_actual_arrival_time           -- 始发中心最早卸车到件实际到车时间

,max(if(center_rank_asc=1, loading_later_scantime, null))                                                  as first_center_loading_later_time                      -- 始发中心最晚装车时间
,max(if(center_rank_asc=1, loading_later_weight, null))                                                    as first_center_loading_later_weight                    -- 始发中心最晚装车扫描对应重量

,max(if(center_rank_asc=1, send_later_scantime, null))                                                     as first_center_send_later_time                         -- 始发中心最晚发件时间
,max(if(center_rank_asc=1, send_later_weight, null))                                                       as first_center_send_later_weight                       -- 始发中心最晚发件扫描对应重量

,max(if(center_rank_asc=1, loading_send_shipment_no            , null ))                                   as first_center_loading_send_shipment_no               -- 始发中心最晚装车发件扫描对应任务号
,max(if(center_rank_asc=1, loading_send_planned_departure_time , null ))                                   as first_center_loading_send_planned_departure_time    -- 始发中心最晚装车发件规划发车时间
,max(if(center_rank_asc=1, loading_send_planned_arrival_time   , null ))                                   as first_center_loading_send_planned_arrival_time      -- 始发中心最晚装车发件规划到车时间
,max(if(center_rank_asc=1, loading_send_actual_departure_time  , null ))                                   as first_center_loading_send_actual_departure_time     -- 始发中心最晚装车发件实际发车时间
,max(if(center_rank_asc=1, loading_send_actual_arrival_time    , null ))                                   as first_center_loading_send_actual_arrival_time       -- 始发中心最晚装车发件实际到车时间
,max(if(center_rank_asc=1, loading_send_next_network_code      , null ))                                   as first_center_loading_send_next_network_code         -- 始发中心最晚装车发件下一站网点编码
,max(if(center_rank_asc=1, loading_send_next_network_type      , null ))                                   as first_center_loading_send_next_network_type         -- 始发中心最晚装车发件下一站网点类型

,min(if(center_rank_desc=1, network_code, null ))                                                          as end_center_network_code                              -- 末端转运中心(目的中心与末端中心哪个最符合需求)
,min(if(center_rank_desc=1, unloading_early_scantime , null ))                                             as end_center_unloading_early_time                      -- 末端中心最早卸车时间
,min(if(center_rank_desc=1, unloading_early_weight , null ))                                               as end_center_unloading_early_weight                    -- 末端中心最早卸车扫描对应重量
,min(if(center_rank_desc=1, arrival_early_scantime , null ))                                               as end_center_arrival_early_time                        -- 末端中心最早到件时间
,min(if(center_rank_desc=1, arrival_early_weight , null ))                                                 as end_center_arrival_early_weight                      -- 末端中心最早到件扫描对应重量
,min(if(center_rank_desc=1, unloading_arrival_shipment_no           , null ))                              as end_center_unloading_arrival_shipment_no             -- 末端中心最早卸车到件扫描对应任务号
,min(if(center_rank_desc=1, unloading_arrival_planned_departure_time, null ))                              as end_center_unloading_arrival_planned_departure_time  -- 末端中心最早卸车到件规划发车时间
,min(if(center_rank_desc=1, unloading_arrival_planned_arrival_time  , null ))                              as end_center_unloading_arrival_planned_arrival_time    -- 末端中心最早卸车到件规划到车时间
,min(if(center_rank_desc=1, unloading_arrival_actual_departure_time , null ))                              as end_center_unloading_arrival_actual_departure_time   -- 末端中心最早卸车到件实际发车时间
,min(if(center_rank_desc=1, unloading_arrival_actual_arrival_time   , null ))                              as end_center_unloading_arrival_actual_arrival_time     -- 末端中心最早卸车到件实际到车时间
,min(if(center_rank_desc=1, loading_later_scantime , null ))                                               as end_center_loading_later_time                        -- 末端中心最晚装车时间
,min(if(center_rank_desc=1, loading_later_weight , null ))                                                 as end_center_loading_later_weight                      -- 末端中心最晚装车扫描对应重量
,min(if(center_rank_desc=1, send_later_scantime , null ))                                                  as end_center_send_later_time                           -- 末端中心最晚发件时间
,min(if(center_rank_desc=1, send_later_weight , null ))                                                    as end_center_send_later_weight                         -- 末端中心最晚发件扫描对应重量
,min(if(center_rank_desc=1, loading_send_shipment_no            , null ))                                  as end_center_loading_send_shipment_no                 -- 末端中心最晚装车发件扫描对应任务号
,min(if(center_rank_desc=1, loading_send_planned_departure_time , null ))                                  as end_center_loading_send_planned_departure_time      -- 末端中心最晚装车发件规划发车时间
,min(if(center_rank_desc=1, loading_send_planned_arrival_time   , null ))                                  as end_center_loading_send_planned_arrival_time        -- 末端中心最晚装车发件规划到车时间
,min(if(center_rank_desc=1, loading_send_actual_departure_time  , null ))                                  as end_center_loading_send_actual_departure_time       -- 末端中心最晚装车发件实际发车时间
,min(if(center_rank_desc=1, loading_send_actual_arrival_time    , null ))                                  as end_center_loading_send_actual_arrival_time         -- 末端中心最晚装车发件实际到车时间
,min(if(center_rank_desc=1, loading_send_next_network_code      , null ))                                  as end_center_loading_send_next_network_code           -- 末端中心最晚装车发件下一站网点编码
,min(if(center_rank_desc=1, loading_send_next_network_type      , null ))                                  as end_center_loading_send_next_network_type           -- 末端中心最晚装车发件下一站网点类型

,min(if(is_end_entreport , network_code , null ))                             as end_nodal_network_code                               -- 末端集散网点
,min(if(is_end_entreport , unloading_early_scantime , null ))                 as end_nodal_unloading_early_time                       -- 末端集散最早卸车时间
,min(if(is_end_entreport , unloading_early_weight , null ))                   as end_nodal_unloading_early_weight                     -- 末端集散最早卸车扫描对应重量
,min(if(is_end_entreport , arrival_early_scantime , null ))                   as end_nodal_arrival_early_time                         -- 末端集散最早到件时间
,min(if(is_end_entreport , arrival_early_weight , null ))                     as end_nodal_arrival_early_weight                       -- 末端集散最早到件扫描对应重量
,min(if(is_end_entreport , unloading_arrival_shipment_no           , null ))  as end_nodal_unloading_arrival_shipment_no              -- 末端集散最早卸车到件扫描对应任务号
,min(if(is_end_entreport , unloading_arrival_planned_departure_time, null ))  as end_nodal_unloading_arrival_planned_departure_time   -- 末端集散最早卸车到件规划发车时间
,min(if(is_end_entreport , unloading_arrival_planned_arrival_time  , null ))  as end_nodal_unloading_arrival_planned_arrival_time     -- 末端集散最早卸车到件规划到车时间
,min(if(is_end_entreport , unloading_arrival_actual_departure_time , null ))  as end_nodal_unloading_arrival_actual_departure_time    -- 末端集散最早卸车到件实际发车时间
,min(if(is_end_entreport , unloading_arrival_actual_arrival_time   , null ))  as end_nodal_unloading_arrival_actual_arrival_time      -- 末端集散最早卸车到件实际到车时间
,min(if(is_end_entreport , loading_later_scantime , null ))                   as end_nodal_loading_later_time                         -- 末端集散最晚装车时间
,min(if(is_end_entreport , loading_later_weight , null ))                     as end_nodal_loading_later_weight                       -- 末端集散最晚装车扫描对应重量
,min(if(is_end_entreport , send_later_scantime , null ))                      as end_nodal_send_later_time                            -- 末端集散最晚发车时间
,min(if(is_end_entreport , send_later_weight , null ))                        as end_nodal_send_later_weight                          -- 末端集散最晚发车扫描对应重量
,min(if(is_end_entreport , loading_send_shipment_no            , null ))      as end_nodal_loading_send_shipment_no                  -- 末端集散最晚装车发车扫描对应任务号
,min(if(is_end_entreport , loading_send_planned_departure_time , null ))      as end_nodal_loading_send_planned_departure_time       -- 末端集散最晚装车发车规划发车时间
,min(if(is_end_entreport , loading_send_planned_arrival_time   , null ))      as end_nodal_loading_send_planned_arrival_time         -- 末端集散最晚装车发车规划到车时间
,min(if(is_end_entreport , loading_send_actual_departure_time  , null ))      as end_nodal_loading_send_actual_departure_time        -- 末端集散最晚装车发车实际发车时间
,min(if(is_end_entreport , loading_send_actual_arrival_time    , null ))      as end_nodal_loading_send_actual_arrival_time          -- 末端集散最晚装车发车实际到车时间
,min(if(is_end_entreport , loading_send_next_network_code      , null ))      as end_nodal_loading_send_next_network_code            -- 末端集散最晚装车发件下一站网点编码
,min(if(is_end_entreport , loading_send_next_network_type      , null ))      as end_nodal_loading_send_next_network_type            -- 末端集散最晚装车发件下一站网点类型

,max(if(all_rank_desc=1, network_code, null))                                 as end_network_network_code                             -- 末端网点网点编码
,max(if(all_rank_desc=1, unloading_early_scantime, null))                     as end_network_unloading_early_time                     -- 末端网点最最早卸车时间
,max(if(all_rank_desc=1, arrival_early_scantime, null))                       as end_network_arrival_early_time                       -- 末端网点最最早到件时间

,min(deliver_early_scantime)                                                  as deliver_early_scantime                               -- 最早派件时间
,max(deliver_later_cols).scantime                                             as deliver_later_scantime                               -- 最晚派件时间
,max(deliver_later_cols).network_code                                         as deliver_later_scansitecode                           -- 最晚派件网点
,max(deliver_later_cols).weight                                               as deliver_later_weight                                 -- 最晚派件重量
,max(sign_later_scantime)                                                     as sign_later_scantime                                  -- 最晚签收时间
,min(sign_early_cols).scantime                                                as sign_early_scantime                                  -- 最早签收时间
,min(sign_early_cols).network_code                                            as sign_early_scansitecode                              -- 最早签收网点
,min(sign_early_cols).weight                                                  as sign_early_weight                                    -- 最早签收重量
,min(aging_sign_cols).scantime                                                as aging_sign_scantime                                  -- 实效签收时间
,min(aging_sign_cols).network_code                                            as aging_sign_scansitecode                              -- 实效签收网点
,min(aging_sign_cols).weight                                                  as aging_sign_weight                                    -- 实效签收重量
,min(aging_sign_cols).scantype                                                as aging_sign_scantype                                  -- 时效签收扫描类型
,min(actual_sign_cols).scantime                                               as actual_sign_scantime                                 -- 实际签收时间
,min(actual_sign_cols).network_code                                           as actual_sign_scansitecode                             -- 实际签收网点
,min(actual_sign_cols).weight                                                 as actual_sign_weight                                   -- 实际签收重量
,min(actual_sign_cols).scantype                                               as actual_sign_scantype                                 -- 实际签收扫描类型
,max(dt)                       as  dt
,max(first_code)               as  first_code
,max(second_code)              as  second_code
,max(third_code)               as  third_code
,max(is_sign)                  as  is_sign
,max(waybill_source_code)      as  waybill_source_code
,max(waybill_source_name)      as  waybill_source_name
,max(is_void)                  as  is_void
,max(is_refund)                as  is_refund

,max(pick_network_code)        as pick_network_code
,max(dispatch_network_code)    as dispatch_network_code
,max(input_network_code)       as input_network_code

,max(order_source_code)        as  order_source_code
,max(is_delete)                as  is_delete
,max(goods_name)               as  goods_name
,max(is_abnormal)              as  is_abnormal
,max(waybill_weight)           as  waybill_weight
,max(settlement_weight)        as  settlement_weight
,max(statistics_order_source)  as  statistics_order_source
,max(goods_type_code)          as  goods_type_code
,max(goods_type_name)          as  goods_type_name
from tab
group by waybill_no
having max(dt) between date_add('{{ execution_date | cst_ds }}', -13) and '{{ execution_date | cst_ds }}'
),
firstcode as (
select code,center_code
from jms_dim.dim_lmdm_sys_first_code
where is_delete = 1  and is_enable = 1
group by code,center_code
)
insert overwrite table jms_dwd.dwd_s01_whole_operations_waybill_dt partition(dt)
select
operat.waybill_no                                       --  运单号
,waybill_first_scantime                                 --  最早操作时间
,waybill_first_scantype                                 --  最早扫描类型
,waybill_end_scantime                                   --  最晚操作时间
,waybill_end_scantype                                   --  最晚操作类型
,all_operation_times                                    --  运单扫描总次数
,center_operation_times                                 --  中心扫描总次数
,entrepot_operation_times                               --  集散网点扫描次数
,all_pass_netowrk_times                                 --  运单总经过网点数
,pass_center_times                                      --  运单总经过中心数
,pass_entrepot_times                                    --  运单总经过集散网点数
,actual_taking_scantime                                 --  实际揽收时间
,actual_taking_scansitecode                             --  实际揽收网点
,actual_taking_weight                                   --  实际揽收重量
,actual_taking_scantype                                 --  实际揽收扫描类型
,taking_later_scantime                                  --  最晚揽收时间
,taking_early_scantime                                  --  最早揽收时间
,taking_early_scansitecode                              --  最早揽收网点
,taking_early_weight                                    --  最早揽收重量
,warehousing_later_scantime                             --  最晚入仓时间
,warehousing_early_scantime                             --  最早入仓时间
,warehousing_early_scansitecode                         --  最早入仓网点
,warehousing_early_weight                               --  最早入仓重量
,bagging_early_scantime                                 --  最晚装袋时间
,bagging_later_scantime                                 --  最早装袋时间
,bagging_later_scansitecode                             --  最早装袋网点
,bagging_later_package_code                             --  最早装袋重量

,first_network_network_code                             --  始发网点网点编码

,first_network_loading_later_time                       --  始发网点最晚装车时间
,first_network_loading_later_weight                     --  始发网点最晚装车扫描对应重量

,first_network_send_later_time                          --  始发网点最晚发车时间
,first_network_send_later_weight                        --  始发网点最晚发车扫描对应重量

,first_network_loading_send_shipment_no                --  始发网点最晚装车发件扫描对应任务号
,first_network_loading_send_planned_departure_time     --  始发网点最晚装车发件规划发车时间
,first_network_loading_send_planned_arrival_time       --  始发网点最晚装车发件规划到车时间
,first_network_loading_send_actual_departure_time      --  始发网点最晚装车发件实际发车时间
,first_network_loading_send_actual_arrival_time        --  始发网点最晚装车发件实际到车时间
,first_network_loading_send_next_network_code          --  始发网点最晚装车发件下一站网点编码
,first_network_loading_send_next_network_type          --  始发网点最晚装车发件下一站网点类型

,first_nodal_network_code                               --  始发集散网点

,first_nodal_unloading_early_time                       --  始发集散最早卸车时间
,first_nodal_unloading_early_weight                     --  始发集散最早卸车扫描对应重量

,first_nodal_arrival_early_time                         --  始发集散最早到件时间
,first_nodal_arrival_early_weight                       --  始发集散最早到件扫描对应重量

,first_nodal_unloading_arrival_shipment_no              --  始发集散最早卸车到件扫描对应任务号
,first_nodal_unloading_arrival_planned_departure_time   --  始发集散最早卸车到件规划发车时间
,first_nodal_unloading_arrival_planned_arrival_time     --  始发集散最早卸车到件规划到车时间
,first_nodal_unloading_arrival_actual_departure_time    --  始发集散最早卸车到件实际发车时间
,first_nodal_unloading_arrival_actual_arrival_time      --  始发集散最早卸车到件实际到车时间

,first_nodal_loading_later_time                         --  始发集散最晚装车时间
,first_nodal_loading_later_weight                       --  始发集散最晚装车扫描对应重量

,first_nodal_send_later_time                            --  始发集散最晚发车时间
,first_nodal_send_later_weight                          --  始发集散最晚发车扫描对应重量
,first_nodal_loading_send_shipment_no                  --  始发集散最晚装车发件扫描对应任务号
,first_nodal_loading_send_planned_departure_time       --  始发集散最晚装车发件规划发车时间
,first_nodal_loading_send_planned_arrival_time         --  始发集散最晚装车发件规划到车时间
,first_nodal_loading_send_actual_departure_time        --  始发集散最晚装车发件实际发车时间
,first_nodal_loading_send_actual_arrival_time          --  始发集散最晚装车发件实际到车时间
,first_nodal_loading_send_next_network_code            --  始发集散最晚装车发件下一站网点编码
,first_nodal_loading_send_next_network_type            --  始发集散最晚装车发件下一站网点类型

,first_center_network_code                              --  始发转运中心

,first_center_unloading_early_time                      --  始发中心最早卸车时间
,first_center_unloading_early_weight                    --  始发中心最早卸车扫描对应重量

,first_center_arrival_early_time                        --  始发中心最早到件时间
,first_center_arrival_early_weight                      --  始发中心最早到件扫描对应重量

,first_center_unloading_arrival_shipment_no             --  始发中心最早卸车到件扫描对应任务号
,first_center_unloading_arrival_planned_departure_time  --  始发中心最早卸车到件规划发车时间
,first_center_unloading_arrival_planned_arrival_time    --  始发中心最早卸车到件规划到车时间
,first_center_unloading_arrival_actual_departure_time   --  始发中心最早卸车到件实际发车时间
,first_center_unloading_arrival_actual_arrival_time     --  始发中心最早卸车到件实际到车时间

,first_center_loading_later_time                        --  始发中心最晚装车时间
,first_center_loading_later_weight                      --  始发中心最晚装车扫描对应重量

,first_center_send_later_time                           --  始发中心最晚发件时间
,first_center_send_later_weight                         --  始发中心最晚发件扫描对应重量

,first_center_loading_send_shipment_no                 --  始发中心最晚装车发件扫描对应任务号
,first_center_loading_send_planned_departure_time      --  始发中心最晚装车发件规划发车时间
,first_center_loading_send_planned_arrival_time        --  始发中心最晚装车发件规划到车时间
,first_center_loading_send_actual_departure_time       --  始发中心最晚装车发件实际发车时间
,first_center_loading_send_actual_arrival_time         --  始发中心最晚装车发件实际到车时间
,first_center_loading_send_next_network_code           --  始发中心最晚装车发件下一站网点编码
,first_center_loading_send_next_network_type           --  始发中心最晚装车发件下一站网点类型

,end_center_network_code                                --  末端转运中心
,end_center_unloading_early_time                        --  末端中心最早卸车时间
,end_center_unloading_early_weight                      --  末端中心最早卸车扫描对应重量
,end_center_arrival_early_time                          --  末端中心最早到件时间
,end_center_arrival_early_weight                        --  末端中心最早到件扫描对应重量
,end_center_unloading_arrival_shipment_no               --  末端中心最早卸车到件扫描对应任务号
,end_center_unloading_arrival_planned_departure_time    --  末端中心最早卸车到件规划发车时间
,end_center_unloading_arrival_planned_arrival_time      --  末端中心最早卸车到件规划到车时间
,end_center_unloading_arrival_actual_departure_time     --  末端中心最早卸车到件实际发车时间
,end_center_unloading_arrival_actual_arrival_time       --  末端中心最早卸车到件实际到车时间
,end_center_loading_later_time                          --  末端中心最晚装车时间
,end_center_loading_later_weight                        --  末端中心最晚装车扫描对应重量
,end_center_send_later_time                             --  末端中心最晚发件时间
,end_center_send_later_weight                           --  末端中心最晚发件扫描对应重量
,end_center_loading_send_shipment_no                   --  末端中心最晚装车发件扫描对应任务号
,end_center_loading_send_planned_departure_time        --  末端中心最晚装车发件规划发车时间
,end_center_loading_send_planned_arrival_time          --  末端中心最晚装车发件规划到车时间
,end_center_loading_send_actual_departure_time         --  末端中心最晚装车发件实际发车时间
,end_center_loading_send_actual_arrival_time           --  末端中心最晚装车发件实际到车时间
,end_center_loading_send_next_network_code             --  末端中心最晚装车发件下一站网点编码
,end_center_loading_send_next_network_type             --  末端中心最晚装车发件下一站网点类型

,end_nodal_network_code                                 --  末端集散网点

,end_nodal_unloading_early_time                         --  末端集散最早卸车时间
,end_nodal_unloading_early_weight                       --  末端集散最早卸车扫描对应重量

,end_nodal_arrival_early_time                           --  末端集散最早到件时间
,end_nodal_arrival_early_weight                         --  末端集散最早到件扫描对应重量

,end_nodal_unloading_arrival_shipment_no                --  末端集散最早卸车到件扫描对应任务号
,end_nodal_unloading_arrival_planned_departure_time     --  末端集散最早卸车到件规划发车时间
,end_nodal_unloading_arrival_planned_arrival_time       --  末端集散最早卸车到件规划到车时间
,end_nodal_unloading_arrival_actual_departure_time      --  末端集散最早卸车到件实际发车时间
,end_nodal_unloading_arrival_actual_arrival_time        --  末端集散最早卸车到件实际到车时间

,end_nodal_loading_later_time                           --  末端集散最晚装车时间
,end_nodal_loading_later_weight                         --  末端集散最晚装车扫描对应重量

,end_nodal_send_later_time                              --  末端集散最晚发车时间
,end_nodal_send_later_weight                            --  末端集散最晚发车扫描对应重量
,end_nodal_loading_send_shipment_no                    --  末端集散最晚装车发件扫描对应任务号
,end_nodal_loading_send_planned_departure_time         --  末端集散最晚装车发件规划发车时间
,end_nodal_loading_send_planned_arrival_time           --  末端集散最晚装车发件规划到车时间
,end_nodal_loading_send_actual_departure_time          --  末端集散最晚装车发件实际发车时间
,end_nodal_loading_send_actual_arrival_time            --  末端集散最晚装车发件实际到车时间
,end_nodal_loading_send_next_network_code              --  末端集散最晚装车发件下一站网点编码
,end_nodal_loading_send_next_network_type              --  末端集散最晚装车发件下一站网点类型

,end_network_network_code                               -- 末端网点网点编码
,end_network_unloading_early_time                       -- 末端网点最最早卸车时间
,end_network_arrival_early_time                         -- 末端网点最最早到件时间

,deliver_early_scantime                                 --  最早派件时间
,deliver_later_scantime                                 --  最晚派件时间
,deliver_later_scansitecode                             --  最晚派件网点
,deliver_later_weight                                   --  最晚派件重量
,sign_later_scantime                                    --  最晚签收时间
,sign_early_scantime                                    --  最早签收时间
,sign_early_scansitecode                                --  最早签收网点
,sign_early_weight                                      --  最早签收重量
,aging_sign_scantime                                    --  实效签收时间
,aging_sign_scansitecode                                --  实效签收网点
,aging_sign_weight                                      --  实效签收重量
,aging_sign_scantype                                    --  实效签收扫描类型
,actual_sign_scantime                                   --  实际签收时间
,actual_sign_scansitecode                               --  实际签收网点
,actual_sign_weight                                     --  实际签收重量
,actual_sign_scantype                                   --  实际签收扫描类型

,first_code                                             --  一段码(运单表获取)
,second_code                                            --  二段码(运单表获取)
,third_code                                             --  三段码(运单表获取)
,is_sign                                                --  签收标识(运单表获取)
,waybill_source_code                                    --  运单来源code(运单表获取)
,waybill_source_name                                    --  运单来源code(运单表获取)
,is_void                                                --  是否作废件,1是，0否(运单表获取)
,is_refund                                              --  是否退件,1是，0否(运单表获取)
,pick_network_code                                      --  寄件网点code(运单表获取)
,dispatch_network_code                                  --  派件网点code(运单表获取)
,input_network_code                                     --  录入网点code(运单表获取)
,order_source_code                                      --  订单来源编码(运单表获取)
,is_delete                                              --  是否删除,1未删除，2已删除(运单表获取)
,goods_name                                             --  物品名称(运单表获取)
,is_abnormal                                            --  问题件标识,1是，0否(运单表获取)
,waybill_weight                                         --  运单重量,单位千克(运单表获取)
,settlement_weight                                      --  结算重量(运单表获取)
,statistics_order_source                                --  订单来源(tab_statistics获取,补充订单来源)

,firstcode.center_code as aim_center_code               --  目的转运中心(通过二段码关联二段码表获取)

,goods_type_code                                        --  物品类型编号
,goods_type_name                                        --  物品类型名称
,dt
from operat
left join firstcode on coalesce(first_code,waybill_no)=firstcode.code
distribute by dt,abs(hash(operat.waybill_no)) % 36   ;
